/**
 * Software License, Version 1.0
 *
 * Copyright 2003 The Trustees of Indiana University.  All rights reserved.
 *
 *
 *Redistribution and use in source and binary forms, with or without
 *modification, are permitted provided that the following conditions are met:
 *
 *1) All redistributions of source code must retain the above copyright notice,
 * the list of authors in the original source code, this list of conditions and
 * the disclaimer listed in this license;
 *2) All redistributions in binary form must reproduce the above copyright
 * notice, this list of conditions and the disclaimer listed in this license in
 * the documentation and/or other materials provided with the distribution;
 *3) Any documentation included with all redistributions must include the
 * following acknowledgement:
 *
 *"This product includes software developed by the Community Grids Lab. For
 * further information contact the Community Grids Lab at
 * http://communitygrids.iu.edu/."
 *
 * Alternatively, this acknowledgement may appear in the software itself, and
 * wherever such third-party acknowledgments normally appear.
 *
 *4) The name Indiana University or Community Grids Lab or NaradaBrokering,
 * shall not be used to endorse or promote products derived from this software
 * without prior written permission from Indiana University.  For written
 * permission, please contact the Advanced Research and Technology Institute
 * ("ARTI") at 351 West 10th Street, Indianapolis, Indiana 46202.
 *5) Products derived from this software may not be called NaradaBrokering,
 * nor may Indiana University or Community Grids Lab or NaradaBrokering appear
 * in their name, without prior written permission of ARTI.
 *
 *
 * Indiana University provides no reassurances that the source code provided
 * does not infringe the patent or any other intellectual property rights of
 * any other entity.  Indiana University disclaims any liability to any
 * recipient for claims brought by any other entity based on infringement of
 * intellectual property rights or otherwise.
 *
 *LICENSEE UNDERSTANDS THAT SOFTWARE IS PROVIDED "AS IS" FOR WHICH NO
 *WARRANTIES AS TO CAPABILITIES OR ACCURACY ARE MADE. INDIANA UNIVERSITY GIVES
 *NO WARRANTIES AND MAKES NO REPRESENTATION THAT SOFTWARE IS FREE OF
 *INFRINGEMENT OF THIRD PARTY PATENT, COPYRIGHT, OR OTHER PROPRIETARY RIGHTS.
 *INDIANA UNIVERSITY MAKES NO WARRANTIES THAT SOFTWARE IS FREE FROM "BUGS",
 *"VIRUSES", "TROJAN HORSES", "TRAP DOORS", "WORMS", OR OTHER HARMFUL CODE.
 *LICENSEE ASSUMES THE ENTIRE RISK AS TO THE PERFORMANCE OF SOFTWARE AND/OR
 *ASSOCIATED MATERIALS, AND TO THE PERFORMANCE AND VALIDITY OF INFORMATION
 *GENERATED USING SOFTWARE.
 */
package cgl.narada.service.storage.db;

import java.sql.SQLException;
import java.util.LinkedList;
import java.util.Vector;

import cgl.narada.event.TemplateInfo;
import cgl.narada.event.impl.TemplateInfoImpl;
import cgl.narada.matching.Profile;
import cgl.narada.matching.ProfileReconstructor;
import cgl.narada.service.ServiceException;
import cgl.narada.service.reliable.CompanionDbInfo;
import cgl.narada.service.storage.InventoryEvent;
import cgl.narada.service.storage.SequenceDestinations;
import cgl.narada.service.storage.StorageService;

/** The MQSL DB implementation of the StorageService
 @author Xi Rao
 $Date$
 $Revision$
 @author modified by Hasan Bulut
 */

public class StorageServiceDbImpl implements StorageService {

    private DataAccessObject storageDao;
//	private DatabaseStatements	statements				= new DatabaseStatements();
    private DatabaseStatements statements = DatabaseStatements.getInstance();

    private boolean debug = false;
    private String moduleName = "rtsp:StorageServiceDbImpl: ";

    private String timestampTableStr = "TimestampInfo";
    private String sequenceNumberStr = "sequenceNumber";
    private String timestampStr = "timestamp";
    private String templateIdStr = "templateID";

    public StorageServiceDbImpl() throws ServiceException {
        DatabaseFactory df = new DatabaseFactory();
        try {
            storageDao = df.getDAO();
            statements.initialize(storageDao.getConnection(), storageDao);
        } catch (Exception ex) {
            System.out.println("exception in creating DataAccessObject ");
            throw new ServiceException(
                    "exception in creating DataAccessObject ");
        }
        checkTableConsistance();
        System.out.println("****Using db.rtsp package");
    }

    // insert into Inventory15Full table and InventoryLight table
//    public synchronized void store(InventoryEvent inventoryEvent,
//                                   SequenceDestinations sequenceDestinations) throws
//            ServiceException {
//        store(inventoryEvent);
//        store(sequenceDestinations, inventoryEvent.getSequenceNumber());
//    }

    public synchronized void store(CompanionDbInfo companionDbInfo,
                                   InventoryEvent inventoryEvent,
                                   SequenceDestinations sequenceDestinations) throws
            ServiceException {
        store(companionDbInfo, inventoryEvent);
        if(companionDbInfo.getStatus() == 1) {
            store(sequenceDestinations, inventoryEvent.getSequenceNumber());
        }
    }

    // insert into EntityTemplate table with syncpont = 0 and catenation = 0;
    public void storeEntityForTemplate(int entityId, int templateId) throws
            ServiceException {
        statements.executeInsertIntoEntityTemplateStmt("" + templateId, ""
                + entityId);
    }

    // insert into Profile table
    // Note: destinationAddress is equal entityId
    public void storeProfile(int templateId, Profile profile) throws
            ServiceException {
        byte[] subscription = profile.getBytes(); //profile.getSubscription();

        int destinationAddress = profile.getDestination();
        String profileId = profile.getProfileId();
        int profileType = profile.getProfileType();
        System.out.println(moduleName + "Stored Profile Type = [" + profileType
                           + "] " + profile);
        statements.executeInsertIntoProfileStmt("" + profileId,
                                                "" + profileType,
                                                subscription, "" + templateId,
                                                "" + destinationAddress);
    }

    // insert into Template table
    public void storeTemplate(TemplateInfo templateInfo) throws
            ServiceException {
        int templateId = templateInfo.getTemplateId();
        int templateType = templateInfo.getTemplateType();
        statements.executeInsertIntoTemplateStmt("" + templateId,
                                                 "" + templateType,
                                                 templateInfo.getBytes());
    }

    // Special Note: eventID column is a dummny column, it should be delete in the future release
    // Insert into InventoryFull and InventoryLight table

    /*
     * Added statement ->  storageDao.executePreparedQuery(insertSeqNumTimestamp);
     * by Hasan Bulut
     */
//    private void store(InventoryEvent inventoryEvent) throws ServiceException {

//        long sequenceNumber = inventoryEvent.getSequenceNumber();
//        long previousSequenceNumber = inventoryEvent.getPreviousSequenceNumber();
//        int templateId = inventoryEvent.getTemplateId();
//        byte[] eventBytes = inventoryEvent.getBytes();
//
//        long timestamp = inventoryEvent.getEvent().getEventHeaders().
//                         getTimeStamp();
//        //		System.out.println("timestamp:" + timestamp);
//        if (debug) {
//            System.out.println(moduleName +
//                               "InventoryEvent.getBytes().length = "
//                               + eventBytes.length);
//        }
//
//        // This is dummy column, it should be remove in the future release!
//        int eventID = 0;
//
//        statements.executeInsertIntoInventoryFullStmt("" + sequenceNumber, ""
//                + previousSequenceNumber, "" + eventID, eventBytes,
//                "" + templateId);
//
//        statements.executeInsertIntoInventoryLightStmt("" + sequenceNumber, ""
//                + previousSequenceNumber, "" + templateId);
//
//        statements.executeInsertIntoTimestampInfoStmt("" + sequenceNumber, ""
//                + timestamp, "" + templateId);
//
//        if (debug) {
//            System.out.println(moduleName +
//                               "STORAGE of InventoryEvent SUCCESSFUL");
//        }
//    }

    private void store(CompanionDbInfo companionDbInfo,
                       InventoryEvent inventoryEvent) throws ServiceException {
        long sequenceNumber = companionDbInfo.getSequenceNumber();
        long previousSequenceNumber = companionDbInfo.getPrevSequenceNumber();
        int templateId = companionDbInfo.getTemplateId();
        int catenation = companionDbInfo.getCatenationNumber();
        int status = companionDbInfo.getStatus();

        if (status == 0) {
            statements.executeInsertIntoInventoryLightStmt("" + sequenceNumber,
                    "" + previousSequenceNumber, "" + templateId,
                    "" + catenation, "" + status);
        } else if (status == 1) {
            byte[] eventBytes = inventoryEvent.getBytes();
            String eventID = inventoryEvent.getEvent().getEventHeaders().
                             getEventId().toString();
            statements.executeInsertIntoInventoryFullStmt("" +
                    sequenceNumber, "" + previousSequenceNumber,
                    "" + eventID, eventBytes, "" + templateId);
            long timestamp = inventoryEvent.getEvent().getEventHeaders().
                             getTimeStamp();
            statements.executeInsertIntoTimestampInfoStmt("" +
                    sequenceNumber, "" + timestamp, "" + templateId);

            if (!companionDbInfo.isUpdate()) {
                statements.executeInsertIntoInventoryLightStmt("" +
                        sequenceNumber, "" + previousSequenceNumber,
                        "" + templateId, "" + catenation, "" + status);
            } else {
                // update inventoryLight
                statements.executeUpdateInventoryLightStatusStmt(status);
            }
            if (debug) {
                System.out.println(moduleName +
                                   "InventoryEvent.getBytes().length = "
                                   + eventBytes.length);
            }
            if (debug) {
                System.out.println(moduleName +
                                   "STORAGE of InventoryEvent SUCCESSFUL");
            }
        } else {
            status = 2;
            statements.executeUpdateInventoryLightStatusStmt(status);
        }
    }

    // Insert into Entity and EntityInventory table
    // fix me : make sure we will insert into Entity table too at this point.
    private void store(SequenceDestinations sequenceDestinations,
                       long sequenceNumber) throws ServiceException {
        int[] desAddr = sequenceDestinations.getDestinations();
        for (int i = 0; i < desAddr.length; i++) {
            //Insert into Entity table, possible duplicate.
            //insertQueryDestination = "insert into Entity values (" + desAddr[i] + ");";
            //Insert into EntityInventory table
            statements.executeInsertIntoEntityInventoryStmt("" + sequenceNumber,
                    ""
                    + desAddr[i]);
        }
    }

    // Insert into Entity table
    public void storeRegisteredEntity(int entityId) throws ServiceException {
        statements.executeInsertIntoEntityStmt("" + entityId);
    }

    // each templateId associate with multiple entity, here, we need to delete record
    // in EntityTemplate table
    // which have exactly entityId and templateId
    public void removeEntityFromTemplate(int entityId, int templateId) throws
            ServiceException {
        statements.executeDeleteFromEntityTemplate15Stmt("" + entityId,
                "" + templateId);
    }

    // remove the record has the same templateId in Template table
    public void removeTemplateManagement(int templateId) throws
            ServiceException {
        statements.executeDeleteFromTemplate13Stmt("" + templateId);
    }

    // remove the reocrd has the same templateId in Profile table
    public void removeProfile(int templateId, Profile profile) throws
            ServiceException {
        statements.executeDeleteFromProfile15Stmt("" + templateId,
                                                  profile.getProfileId());
    }

    // rmove the entity from entity table by using specific entity_id
    public void removeRegisteredEntity(int entityId) throws ServiceException {
        statements.executeDeleteFromEntity13Stmt("" + entityId);
    }

    // retrieve the records from Template table
    // fixme: create template info
    public TemplateInfo[] getListOfManagedTemplates() throws ServiceException {
        java.sql.ResultSet rs = statements.
                                executeSelectTemplateBytesFromTemplateStmt();

        //java.sql.ResultSet rs = storageDao.executePreparedQuery(searchQueryTemplate);
        Vector rsVector = new Vector();
        try {
            while (rs.next()) {
                //Blob blob = rs.getBlob(1);
                //        int len = (int) blob.length();
                //        byte[] templateBytes = blob.getBytes(1, len);
                byte[] templateBytes = rs.getBytes(1);
                TemplateInfo templateInfo = new TemplateInfoImpl(templateBytes);
                rsVector.add(templateInfo);
            }
            //added to fix memory leak
            rs.close();
        } catch (SQLException ex) {
            throw new ServiceException(moduleName
                                       +
                                       "Sql exception druing retrieve value from Template table");
        }

        System.out.println(moduleName + "Number of Managed templates = "
                           + rsVector.size());
        TemplateInfoImpl[] templates = (TemplateInfoImpl[]) rsVector
                                       .toArray(new TemplateInfoImpl[0]);
        return templates;

    }

    // given templateId and retrieve the entityId associate with it from the EntityTemplate table
    public int[] getListOfRegisteredEntities(int templateId) throws
            ServiceException {
        java.sql.ResultSet rs = statements.
                                executeSelectEntityIdFromEntityTemplateStmt("" +
                templateId);
        Vector rsVector = new Vector();
        try {
            while (rs.next()) {
                rsVector.add(new Integer(rs.getString(1)));
            }
            //		added to fix memory leak
            rs.close();
        } catch (SQLException ex) {
            throw new ServiceException(moduleName
                                       +
                                       "sql exception during retrieve value frome EntityTemplate table");
        }
        int[] entities = new int[rsVector.size()];
        for (int i = 0; i < rsVector.size(); i++) {
            entities[i] = ((Integer) rsVector.elementAt(i)).intValue();
        }
        return entities;
    }

    // given templateId and retrieve the Profile associate with it from the Profile table
    public Profile[] getListOfRegisteredProfiles(int templateId) throws
            ServiceException {
        java.sql.ResultSet rs = statements.
                                executeSelectProfileTypeSubscriptionFromProfileStmt(
                                        "" +
                                        templateId);

        Vector rsVector = new Vector();
        try {
            while (rs.next()) {
                int profileType = Integer.parseInt(rs.getString(1));
                System.out.println(moduleName + "Profile Type = " + profileType);

                //        Blob blob = rs.getBlob(2);
                //        int len = (int) blob.length();
                //        byte[] profileBytes = blob.getBytes(1, len);
                byte[] profileBytes = rs.getBytes(2);
                Profile profile = ProfileReconstructor.getProfile(profileType,
                        profileBytes);
                rsVector.add(profile);
            }
            //		added to fix memory leak
            rs.close();
        } catch (SQLException ex) {
            throw new ServiceException(moduleName +
                                       "sql exception during retrieve"
                                       + " value from Profile table");
        }
        return (Profile[]) rsVector.toArray(new Profile[0]);
    }

    // given sequenceNumber and retrieve the Inventory assoicate with it from the Inventory table
    public InventoryEvent getStoredEvent(long sequenceNumber) throws
            ServiceException {
        java.sql.ResultSet rs = statements.
                                executeSelectEventFromInventoryFullStmt("" +
                sequenceNumber);
        boolean foundEvent = false;
        boolean badInventory = false;

        try {
            while (rs.next()) {
                foundEvent = true;
                //        Blob blob = rs.getBlob(1);
                //        int len = (int) blob.length();
                //        byte[] ieBytes = new byte[len];
                //        ieBytes = blob.getBytes(1, len);
                byte[] ieBytes = rs.getBytes(1);
                if (ieBytes == null) {
                    badInventory = true;
                }
                InventoryEvent ie = new InventoryEvent(ieBytes);
                rs.close();
                if (badInventory) {
                    System.out.println("******** 1");
                    System.out.println(moduleName + " Bad Inventory");
                    System.out.println("******** 1");
                }
                if (!foundEvent) {
                    System.out.println("********");
                    System.out.println(moduleName + " Event not found");
                    System.out.println("********");
                }
                return ie;
            }

            //		added to fix memory leak
            rs.close();
        } catch (SQLException ex) {
            throw new ServiceException("sql exception during retrieve value "
                                       + "from Inventory table");
        }
        if (badInventory) {
            System.out.println("******** 2");
            System.out.println(moduleName + " Bad Inventory");
            System.out.println("******** 2");
        }
        if (!foundEvent) {
            System.out.println("********");
            System.out.println(moduleName + " Event not found");
            System.out.println("********");
        }
        return null;
    }

    // set the syncpoint value in EntityTemplate table with equal templateId
    // and entityId
    public void advanceSyncpoint(long syncpoint, int templateId, int entityId) throws
            ServiceException {
        statements.executeUpdateEntityTemplate16Stmt("" + syncpoint,
                "" + templateId, "" + entityId);
    }

    // set the catenation value into EntityTemplate table with equal
    // templatedId and entityId
    public void advanceCatenation(int catenation, int templateId, int entityId) throws
            ServiceException {
        statements.executeUpdateEntityTemplate26Stmt("" + catenation,
                "" + templateId, "" + entityId);
        if (debug) {
            System.out.println(moduleName + "Advanced Catenation to " +
                               catenation);
        }
    }

    // get the syncpoint value in EntityTemplate table with equal templateId and entityId
    public long getSyncpoint(int templateId, int entityId) throws
            ServiceException {
        long startTime, elapsedTime;
        startTime = System.currentTimeMillis();
        java.sql.ResultSet rs = statements
                                .executeSelectSyncpointFromEntityTemplateStmt(
                                        "" + templateId, ""
                                        + entityId);
        try {
            while (rs.next()) {
                long syncpoint = Long.parseLong(rs.getString(1));

                elapsedTime = System.currentTimeMillis() - startTime;
                if (debug) {
                    System.out.println(moduleName +
                                       "Time to retrieve sync point = "
                                       + elapsedTime + " mSecs");
                }
                return syncpoint;
            }
            //		added to fix memory leak
            rs.close();

        } catch (SQLException ex) {
            throw new ServiceException(moduleName
                                       +
                                       "sql execption during retrieve syncpoint value");
        } catch (NumberFormatException ex) {
            throw new ServiceException(moduleName
                                       +
                                       "Number Format exception during geSyncpoint");
        }
        return 0;
    }

    // get the catenation value in EntityTemplate table with equal templateId and entityId
    public int getCatenation(int templateId, int entityId) throws
            ServiceException {
        java.sql.ResultSet rs = statements
                                .executeSelectCatenationFromEntityTemplateStmt(
                                        "" + templateId, ""
                                        + entityId);
        try {
            while (rs.next()) {
                int catenation = Integer.parseInt(rs.getString(1));
                if (debug) {
                    System.out.println(moduleName + "Stored Catenation is " +
                                       catenation);
                }
                return catenation;
            }
            //		added to fix memory leak
            rs.close();

        } catch (SQLException ex) {
            throw new ServiceException(moduleName
                                       +
                                       "sql execption during retrieve syncpoint value");
        } catch (NumberFormatException ex) {
            throw new ServiceException(moduleName
                                       +
                                       "Number Format exception during geSyncpoint");
        }
        return 0;
    }

    // get the last sequence number (SN), which is the biggest SN in both Inventory and
    // EntityInventory table. If the bigest SN in two table are not equal, we need to return the small one
    // and remove the bigger one's row from correspond table. The reason is that they are not sync.
    // Fixme: we need to re-think for the logic becasue we add the InventoryLight table.
    public long getSequenceNumberLastAssigned() throws ServiceException {

        java.sql.ResultSet rsInventory = statements
                                         .
                                         executeSelectMAXSeqNumberFromInventoryLight1Stmt();
        long snInventory = getScalarValue(rsInventory);

        java.sql.ResultSet rsEntityInventory = statements
                                               .
                                               executeSelectMAXSeqNumberFromEntityInventoryStmt();
        long snEntityInventory = getScalarValue(rsEntityInventory);
        if (snInventory == snEntityInventory) {

            // added to fix memory leak
            try {
                rsInventory.close();
                rsEntityInventory.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            return snInventory;
        } else if (snInventory < snEntityInventory) {
            // remove the bigger one's row from table because of inconsistence
            statements.executeDeleteFromEntityInventory13Stmt("" +
                    snEntityInventory);
            try {
                rsInventory.close();
                rsEntityInventory.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            return snInventory;
        } else if (snInventory > snEntityInventory) {
            statements.executeDeleteFromInventoryFull13Stmt("" + snInventory);
            statements.executeDeleteFromInventoryLight13("" + snInventory);
            // added to fix memory leak
            try {
                rsInventory.close();
                rsEntityInventory.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            return snEntityInventory;
        }
        // added to fix memory leak
        try {
            rsInventory.close();
            rsEntityInventory.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return 0;
    }

    // get the last sequence number(SN) from Inventory table where the templateId is equal the specific
    // templateId
    public long getSequenceNumberLastAssigned(int templateId) throws
            ServiceException {
        java.sql.ResultSet rs = statements
                                .
                                executeSelectMAXSeqNumberFromInventoryLight1Stmt();
        long scalarValue = getScalarValue(rs);
        // added to fix memory leak
        try {
            rs.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return scalarValue;
    }

    private long getScalarValue(java.sql.ResultSet rs) throws ServiceException {
        try {
            while (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException ex) {
            throw new ServiceException(moduleName +
                                       "error in get the scalar value");
        }
        throw new ServiceException(moduleName
                                   + " have problem to get the Scalar value");

    }

    /* Each sequence number associate with multiple destination
     address(entity Id), given sequenceNumber and entityId. We need operate on
     the InventryEntity table to set the given entityId = 0; */
    public void processAcknowledgement(long sequenceNumber, int entityId) throws
            ServiceException {
        // we need to see if there is exist (sequenceNumber, 0) pair in the EntityInventory table
        // if it is not exist, we will update the (sequenceNumber, entityId) pair to (sequenceNumber, 0) pair,
        // otherwise, we will delete the (sequenceNumber, entityId) record.
        boolean existInTable = false;
        java.sql.ResultSet rs = statements
                                .executeSelectAllFromEntityInventoryStmt("" +
                sequenceNumber);
        try {
            if (rs.next()) {
                existInTable = true;
            }
        } catch (SQLException ex) {
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            throw new ServiceException(moduleName +
                                       "processAcknowledgement error");
        }
        if (existInTable) {
            statements.executeDeleteFromEntityInventory25Stmt("" +
                    sequenceNumber, "" + entityId);
        } else {
            statements.executeUpdateEntityInventory16Stmt("0",
                    "" + sequenceNumber, "" + entityId);
        }
        if (debug) {
            System.out.println(moduleName +
                               "PROCESSED Acknowledgment successfully");
        }
        // added to fix memory leak
        try {
            rs.close();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public long[] checkIfSequencesBelongToTemplate(int templateId,
            long[] sequences) throws ServiceException {
        java.sql.ResultSet rs = statements.
                                executeSelectSeqNumberFromInventoryLight15Stmt(
                                        ""
                                        + templateId);
        Vector sequenceVector = new Vector();
        int sequencesLength = sequences.length;
        try {
            while (rs.next()) {
                Long sLong = new Long(rs.getString(1));
                long longVal = sLong.longValue();
                for (int i = 0; i < sequencesLength; i++) {
                    if (longVal != sequences[i]) {
                        sequenceVector.add(sLong);
                    }
                }
            }
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (SQLException ex) {
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            throw new ServiceException(moduleName +
                                       "retrieveMessedSequence error");
        }

        if (sequenceVector.size() == 0) {
            return null;
        }

        int vectorSize = sequenceVector.size();
        long[] seqNumbers = new long[vectorSize];
        for (int i = 0; i < vectorSize; i++) {
            seqNumbers[i] = ((Long) sequenceVector.elementAt(i)).longValue();
        }
        return seqNumbers;

    }

    /*
     * Added for rtsp replay service
     * by Hasan Bulut
     */
    public LinkedList retrieveSequencesAndTimestamps(int templateId) throws
            ServiceException {

        //		System.out.println(searchForSequenceAndTimestamp);
        java.sql.ResultSet rs = statements.
                                executeSelectSeqNumberTimestampFromTimestampInfo15Stmt(
                                        "" +
                                        templateId);
        ;
        LinkedList seqTsList = new LinkedList();

        try {
            while (rs.next()) {
                seqTsList.add(rs.getString(1));
                //        System.out.println(rs.getString(1));
                seqTsList.add(rs.getString(2));
                //        System.out.println(rs.getString(2));
            }
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (SQLException ex) {
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            throw new ServiceException(moduleName +
                                       "retrieveMessedSequence error");
        }
        if (seqTsList.size() == 0) {
            return null;
        }
        return seqTsList;
    }

    public LinkedList retrieveSequencesAndTimestamps(long startingTimestamp,
            long endingTimestamp, int templateId) throws ServiceException {
        java.sql.ResultSet rs = statements
                                .
                                executeSelectSeqNumberTimestampFromTimestampInfo29Stmt(
                                        "" + templateId, "" + startingTimestamp,
                                        "" + +endingTimestamp);
        LinkedList seqTsList = new LinkedList();

        try {
            while (rs.next()) {
                seqTsList.add(rs.getString(1));
                seqTsList.add(rs.getString(2));
            }
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (SQLException ex) {
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            throw new ServiceException(moduleName +
                                       "retrieveMessedSequence error");
        }
        if (seqTsList.size() == 0) {
            return null;
        }
        return seqTsList;
    }

    public long retrieveTimestamp(int templateId, long sequenceNumber) throws
            ServiceException {
        java.sql.ResultSet rs = statements
                                .executeSelectTimestampFromTimestampInfoStmt("" +
                templateId, ""
                + sequenceNumber);
        long result = 0;
        try {
            while (rs.next()) {
                result = rs.getLong(1);
                //        System.out.println(rs.getLong(1));
                return result;
            }
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (SQLException ex) {
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            throw new ServiceException(moduleName +
                                       "retrieveMessedSequence error");
        }
        return result;
    }

    public LinkedList retrieveMinimumSequeceNumberAndTimestamp(int templateId) throws
            ServiceException {
        LinkedList result = new LinkedList();
        java.sql.ResultSet rs = statements
                                .
                                executeSelectMINSeqNumberTimestampFromTimestampInfoStmt(
                                        ""
                                        + templateId);
        String minimumSeqNum = null;
        String minimumTimestamp = null;
        try {
            //			System.out.println("rs: " + rs.toString());
            while (rs.next()) {
                minimumSeqNum = rs.getString(1);
                minimumTimestamp = rs.getString(2);
                System.out.println("minimumSeqNum:" + minimumSeqNum
                                   + " minimumTimestamp:" + minimumTimestamp);
                result.add(minimumSeqNum);
                result.add(minimumTimestamp);
                return result;
            }
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (SQLException ex) {
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            throw new ServiceException(moduleName +
                                       "retrieveMessedSequence error");
        }
        System.out.println("result size:" + result.size());
        return result;
    }

    public LinkedList retrieveMaximumSequeceNumberAndTimestamp(int templateId) throws
            ServiceException {
        LinkedList result = new LinkedList();
        java.sql.ResultSet rs = statements
                                .
                                executeSelectMAXSeqNumberTimestampFromTimestampInfoStmt(
                                        ""
                                        + templateId);
        long maximumSeqNum = 0;
        long maximumTimestamp = 0;
        try {
            while (rs.next()) {
                maximumSeqNum = rs.getLong(1);
                maximumTimestamp = rs.getLong(2);
                System.out.println("maximumSeqNum:" + maximumSeqNum
                                   + " maximumTimestamp:" + maximumTimestamp);
                result.add(Long.toString(maximumSeqNum));
                result.add(Long.toString(maximumTimestamp));
                return result;
            }
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (SQLException ex) {
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            throw new ServiceException(moduleName +
                                       "retrieveMessedSequence error");
        }
        return result;
    }

    /*
     * Added for rtsp replay service
     * by Hasan Bulut
     */
    public LinkedList retrieveSequencesAndTimestampsStartingAt(
            long startingSequenceNumber, long endingTimestamp, int templateId) throws
            ServiceException {

        java.sql.ResultSet rs = statements
                                .
                                executeSelectSeqNumberTimestampFromTimestampInfo39Stmt(
                                        "" + templateId,
                                        "" + startingSequenceNumber,
                                        "" + endingTimestamp);

        LinkedList seqTsList = new LinkedList();

        try {
            while (rs.next()) {
                seqTsList.add(rs.getString(1));
                seqTsList.add(rs.getString(2));
            }
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (SQLException ex) {
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            throw new ServiceException(moduleName +
                                       "retrieveMessedSequence error");
        }
        if (seqTsList.size() == 0) {
            return null;
        }
        return seqTsList;
    }

    /*
     * Added for rtsp replay service
     * by Hasan Bulut
     */
    public LinkedList retrieveSequencesByTimestamps(long startingTimestamp,
            long endingTimestamp, int templateId) throws ServiceException {
        java.sql.ResultSet rs = statements
                                .executeSelectSeqNumberFromTimestampInfoStmt("" +
                templateId, ""
                + startingTimestamp, "" + endingTimestamp);
        LinkedList seqTsList = new LinkedList();

        try {
            while (rs.next()) {
                seqTsList.add(rs.getString(1));
                seqTsList.add(rs.getString(2));
            }
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (SQLException ex) {
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            throw new ServiceException(moduleName +
                                       "retrieveMessedSequence error");
        }
        if (seqTsList.size() == 0) {
            return null;
        }
        return seqTsList;
    }

    /*
     * Added for rtsp replay service
     * by Hasan Bulut
     */
    public long getTimestamp(long sequenceNumber, int templateId) throws
            ServiceException {
        long ts = 0;
        java.sql.ResultSet rs = statements
                                .executeSelectTimestampFromTimestampInfoStmt("" +
                templateId, ""
                + sequenceNumber);
        try {
            while (rs.next()) {
                ts = rs.getLong(1);
                return ts;
            }
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (SQLException ex) {
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            throw new ServiceException(moduleName +
                                       "retrieveMessedSequence error");
        }
        return ts;
    }

    public long[] retrieveSequences(long startingAt, long endingAt,
                                    int templateId, int maxOccur) throws
            ServiceException {

        java.sql.ResultSet rs = statements
                                .executeSelectSeqNumberFromInventoryLight29Stmt(
                                        "" + startingAt, ""
                                        + endingAt, "" + templateId);
        // add group by clause, hope it is return the uniquce sequence number
        Vector rsVector = new Vector();
        int counter = 0;

        try {
            while (rs.next()) {
                if (counter >= maxOccur) {
                    break;
                }
                rsVector.add(new Long(rs.getString(1)));
                counter++;
            }
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (SQLException ex) {
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            throw new ServiceException(moduleName +
                                       "retrieveMessedSequence error");
        }
        if (rsVector.size() == 0) {
            return null;
        }

        int vectorSize = rsVector.size();
        long[] seqNumbers = new long[vectorSize];
        for (int i = 0; i < vectorSize; i++) {
            seqNumbers[i] = ((Long) rsVector.elementAt(i)).longValue();
        }
        return seqNumbers;
    }

    // retrive missed sequence number, which is between the range and with specific templateId and entityId
    public long[] retrieveMissedSequences(long startingAt, long endingAt,
                                          int templateId, int entityId) throws
            ServiceException {
        long startTime, elapsedTime;
        startTime = System.currentTimeMillis();
        java.sql.ResultSet rs = statements
                                .
                                executeSelectSeqNumberFromEntityInventoryInventoryLightStmt(
                                        ""
                                        + startingAt, "" + endingAt,
                                        "" + entityId, "" + templateId);
        // add group by clause, hope it is return the uniquce sequence number
        Vector rsVector = new Vector();
        try {
            while (rs.next()) {
                rsVector.add(new Long(rs.getString(1)));
            }
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (SQLException ex) {
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            throw new ServiceException(moduleName +
                                       "retrieveMessedSequence error");
        }
        if (rsVector.size() == 0) {
            return null;
        }
        long[] missSeq = new long[rsVector.size()];
        for (int i = 0; i < missSeq.length; i++) {
            missSeq[i] = ((Long) rsVector.elementAt(i)).longValue();
        }
        elapsedTime = System.currentTimeMillis() - startTime;
        if (debug) {
            System.out.println(moduleName +
                               "Time to retrieve missed sequences = "
                               + elapsedTime + " mSecs");
        }
        return missSeq;
    }

    // get list of entityId from from Entity table
    public int[] getListOfRegisteredEntities() throws ServiceException {
        java.sql.ResultSet rs = statements.executeSelectAllFromEntity9Stmt();
        Vector rsVector = new Vector();
        try {
            while (rs.next()) {
                rsVector.add(new Integer(rs.getString(1)));
            }
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (SQLException ex) {
            // added to fix memory leak
            try {
                rs.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            throw new ServiceException(moduleName + "retrievent Entity error");
        }
        if (rsVector.size() == 0) {
            return null;
        }
        int[] registeredEntities = new int[rsVector.size()];
        for (int i = 0; i < registeredEntities.length; i++) {
            registeredEntities[i] = ((Integer) rsVector.elementAt(i)).intValue();
        }
        return registeredEntities;
    }

    private void checkTableConsistance() {
        try {
            java.sql.ResultSet rsLight = statements.
                                         executeSelectMAXSeqNumberFromInventoryLight1Stmt();
            long maxLight = this.getScalarValue(rsLight);

            java.sql.ResultSet rsFull = statements.
                                        executeSelectMAXSeqNumberFromInventoryFullStmt();
            long maxFull = this.getScalarValue(rsFull);

            if (maxLight > maxFull) {
                statements.executeDeleteFromInventoryLight13("" + maxLight);
            } else if (maxLight < maxFull) {
                statements.executeDeleteFromInventoryFull13Stmt("" + maxFull);
            }
            // added to fix memory leak
            try {
                rsFull.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        } catch (ServiceException ex) {

            ex.printStackTrace();
        }

    }
}
